package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;

import model.OrderItem;
import oracle.jdbc.pool.OracleDataSource;

public class OrderItemOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public OrderItemOperations()
	{
		conn=openDB();
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected orderitem.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	
	// drop orderitem table from database
	public void dropOrderItemTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();
				stm.execute("DROP TABLE Orderitem");
				System.out.println("Order Item table dropped.");
			
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	
	// create a order item table in database
		public void CreateOrderItemTable() 
		{
			try 
			{
				stm = conn.createStatement();
				String create = "CREATE TABLE OrderItem "
						+ "(ord_num NUMBER,day_id NUMBER,prod_id NUMBER,prod_qty NUMBER, "
						+ "PRIMARY KEY (ord_num,day_id,prod_id),FOREIGN KEY (prod_id) REFERENCES Product,"
						+ "FOREIGN KEY (ord_num,day_id) REFERENCES Day)";

				stm.executeUpdate(create);
				System.out.println("orderitem table created");
				
			} 
			catch (SQLException e) 
			{
				System.out.print("SQL Exception " + e);
				System.exit(1);
			}
		}
		
		// delete a customer from table
		public void deleteCustomer(int i) 
		{
			try 
			{
				String cmd = "DELETE FROM Customer WHERE cust_id =" + i;
				stm = conn.createStatement();
				stm.executeUpdate(cmd);
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
		}
		// add a order item to the table
		public void addOrderItem(int o,int d,int p,int q) 
		{
			try 
			{
				String sql = "INSERT INTO ORDERITEM(ord_num,day_id,prod_id,prod_qty) VALUES (?,?,?,?)";

				prst = conn.prepareStatement(sql);
				prst.setInt(1, o);
				prst.setInt(2, d);
				prst.setInt(3, p);
				prst.setInt(4, q);
				

				prst.executeUpdate();
			} 
			catch (Exception se) 
			{
				System.out.println(se);
			}
		}
		// return a result set of all customers in table
		public ResultSet getCustomer() 
		{
			try 
			{
				String queryString = "SELECT * FROM Customer ORDER BY cust_id";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return rst;
		}
		
		// close the connection to database
		public void closeDB() 
		{
			try 
			{
				stm.close();
				rst.close();
				conn.close();
				System.out.print("Connection closed orderitem");
			} 
			catch (SQLException e) 
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
		
		public void closeDB2() 
		{
			try 
			{
				stm.close();
				//rst.close();
				conn.close();
				System.out.print("Connection closed orderitem");
			} 
			catch (SQLException e) 
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
		// close the connection to database
				public void closeDB3() 
				{
					try 
					{
						//stm.close();
						//rst.close();
						conn.close();
						System.out.print("Connection closed orderitem");
					} 
					catch (SQLException e) 
					{
						System.out.print("Could not close connection ");
						e.printStackTrace();
					}
				}
}
